﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;
using HHMMS.FileServer;

namespace HHMMS.FileServer
{
	/// <summary>
	///封装了对表AttachmentTypeInfo的数据库操作的方法。
	/// </summary>
    public class AttachmentTypeInfoProvider
    {
		#region 字段属性
		//默认数据库连接字符串名称
		private readonly string connectionName = ConfigurationManager.AppSettings["DefaultConnectionName"];
		
		private static AttachmentTypeInfoProvider instance = null;
		/// <summary>
		/// 获取AttachmentTypeInfoProvider类的唯一实例。
		/// </summary>
        public static AttachmentTypeInfoProvider Instance
        {
            get
            {
                if (instance == null)
                {
                    lock (typeof(AttachmentTypeInfoProvider))
                    {
                        instance = new AttachmentTypeInfoProvider();
                    }
                }
                return instance;
            }
        }
		
		/// <summary>
		/// 私有构造函数。
		/// </summary>
		private AttachmentTypeInfoProvider()
		{}
		#endregion
		
		#region 基本操作
		///<summary>
		///删除表AttachmentTypeInfo中的指定记录并返回状态。
		///</summary>
		///<param name="iD">类型ID</param>
		///<returns>删除成功则返回true，否则返回false</returns>
		public bool Delete(string iD)
		{
			string sql = "DELETE [AttachmentTypeInfo] WHERE ID = @ID";
			Database db = DatabaseFactory.CreateDatabase(connectionName);
            DbCommand cmd = db.GetSqlStringCommand(sql);
			db.AddInParameter(cmd, "@ID", DbType.AnsiStringFixedLength, iD);
			return db.ExecuteNonQuery(cmd) > 0;
		}
		
		///<summary>
		///向表AttachmentTypeInfo中插入一条记录并返回状态。
		///</summary>
		///<param name="attachmentTypeInfo">要插入记录的AttachmentTypeInfo实例</param>
		///<returns>返回true或false</returns>
		public bool Insert(AttachmentTypeInfo attachmentTypeInfo)
		{
			string sql = "INSERT INTO [AttachmentTypeInfo] (ID, TypeName, Description, AllowExt, MaxFileSize, SaveDirName) VALUES (@ID, @TypeName, @Description, @AllowExt, @MaxFileSize, @SaveDirName)";
			Database db = DatabaseFactory.CreateDatabase(connectionName);
            DbCommand cmd = db.GetSqlStringCommand(sql);
			db.AddInParameter(cmd, "@ID", DbType.AnsiStringFixedLength, attachmentTypeInfo.ID);
			db.AddInParameter(cmd, "@TypeName", DbType.String, attachmentTypeInfo.TypeName);
			db.AddInParameter(cmd, "@Description", DbType.String, attachmentTypeInfo.Description);
			db.AddInParameter(cmd, "@AllowExt", DbType.AnsiString, attachmentTypeInfo.AllowExt);
			db.AddInParameter(cmd, "@MaxFileSize", DbType.Decimal, attachmentTypeInfo.MaxFileSize);
			db.AddInParameter(cmd, "@SaveDirName", DbType.AnsiString, attachmentTypeInfo.SaveDirName);
			return db.ExecuteNonQuery(cmd) > 0;
		}
		
		///<summary>
		///更新表AttachmentTypeInfo中指定主码的某条记录。
		///</summary>
		///<param name="attachmentTypeInfo">要更新记录的AttachmentTypeInfo实例</param>
		///<returns>更新成功则返回true，否则返回false。</returns>
		public bool Update(AttachmentTypeInfo attachmentTypeInfo)
		{
			string sql = "UPDATE [AttachmentTypeInfo] SET  ID = @ID,  TypeName = @TypeName,  Description = @Description,  AllowExt = @AllowExt,  MaxFileSize = @MaxFileSize,  SaveDirName = @SaveDirName WHERE ID = @ID";
			Database db = DatabaseFactory.CreateDatabase(connectionName);
            DbCommand cmd = db.GetSqlStringCommand(sql);
			db.AddInParameter(cmd, "@ID", DbType.AnsiStringFixedLength, attachmentTypeInfo.ID);
			db.AddInParameter(cmd, "@TypeName", DbType.String, attachmentTypeInfo.TypeName);
			db.AddInParameter(cmd, "@Description", DbType.String, attachmentTypeInfo.Description);
			db.AddInParameter(cmd, "@AllowExt", DbType.AnsiString, attachmentTypeInfo.AllowExt);
			db.AddInParameter(cmd, "@MaxFileSize", DbType.Decimal, attachmentTypeInfo.MaxFileSize);
			db.AddInParameter(cmd, "@SaveDirName", DbType.AnsiString, attachmentTypeInfo.SaveDirName);
			return db.ExecuteNonQuery(cmd) > 0;
		}
		#endregion
		
		#region 获取对象
		///<summary>
		///获取表AttachmentTypeInfo中指定主码的某条记录的实例。
		///</summary>
		///<param name="iD">类型ID</param>
		///<returns>返回记录的实例AttachmentTypeInfo</returns>
		public AttachmentTypeInfo SelectSingle(string iD)
		{
			string sql = "SELECT * FROM [AttachmentTypeInfo] WHERE ID = @ID";
			Database db = DatabaseFactory.CreateDatabase(connectionName);
            DbCommand cmd = db.GetSqlStringCommand(sql);
			db.AddInParameter(cmd, "@ID", DbType.AnsiStringFixedLength, iD);
			using (IDataReader dr = db.ExecuteReader(cmd))
            {
                if (dr != null && dr.Read())
                {
                    return this.BuildEntity(dr);
                }
            }
			return null;
		}
		
		///<summary>
		///根据SQL查询条件返回行实例的集合。
		///</summary>
		///<param name="where">带"WHERE"的SQL查询条件</param>
		///<param name="whereParameters">条件中的参数信息数组</param>
		///<returns>返回的实例AttachmentTypeInfoList对象。</returns>
		public List<AttachmentTypeInfo> SelectMany(string where, params DbParameter[] dbParameters)
		{
			List<AttachmentTypeInfo> attachmentTypeInfoList = new List<AttachmentTypeInfo>();
			string sql = string.Format("SELECT * FROM [AttachmentTypeInfo] {0}", where);
			Database db = DatabaseFactory.CreateDatabase(connectionName);
            DbCommand cmd = db.GetSqlStringCommand(sql);
            if(dbParameters != null && dbParameters.Length > 0)
			{
            	cmd.Parameters.AddRange(dbParameters);
			}
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                if (dr != null)
                {
                	while(dr.Read())
					{
						attachmentTypeInfoList.Add(this.BuildEntity(dr));
					}
                }
            }
			return attachmentTypeInfoList;
		}
		
		///<summary>
		///返回表AttachmentTypeInfo所有行实例的集合。
		///</summary>
		///<returns>返回所有行实例AttachmentTypeInfoList对象。</returns>
		public List<AttachmentTypeInfo> SelectAll()
		{
			return this.SelectMany(string.Empty);
		}
		
		///<summary>
		/// 根据条件和排序按页返回表AttachmentTypeInfo的行实例的集合。
		///</summary>
		///<param name="pageSize">每页显示的条数</param>
		///<param name="pageIndex">页索引(1开始)</param>
		///<param name="where">带"WHERE"的SQL查询条件</param>
		///<param name="order">带"ORDER"的SQL排序视图达式</param>
		///<param name="whereParameters">WHERE中的参数数组</param>
		///<param name="recordCount">返回记录总数</param>
		///<returns>返回某页的所有行实例AttachmentTypeInfo的集合</returns>
		public List<AttachmentTypeInfo> SelectPagedResult(int pageSize, int pageIndex, string where, string order, 
			params DbParameter[] whereParameters)
		{
			if(string.IsNullOrEmpty(order))
				order = "ORDER BY ID DESC";
			
			string sql = @"WITH tmp AS
				(
					SELECT *,ROW_NUMBER() OVER({0}) AS  'RowNumber' FROM [AttachmentTypeInfo] {1}
				)
				SELECT * FROM tmp WHERE RowNumber BETWEEN {2} AND {3}";
			
			sql = string.Format(sql, order, where, (pageIndex - 1) * pageSize + 1, pageIndex * pageSize);
			
			List<AttachmentTypeInfo> attachmentTypeInfoList = new List<AttachmentTypeInfo>();
			
			Database db = DatabaseFactory.CreateDatabase(connectionName);
            DbCommand cmd = db.GetSqlStringCommand(sql);
            if(whereParameters != null && whereParameters.Length > 0)
			{
            	cmd.Parameters.AddRange(whereParameters);
			}
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                if (dr != null)
                {
                	while(dr.Read())
					{
						attachmentTypeInfoList.Add(this.BuildEntity(dr));
					}
                }
            }
			return attachmentTypeInfoList;
		}
		
		///<summary>
		/// 根据条件和排序按页返回表AttachmentTypeInfo的行数。
		///</summary>
		///<param name="where">带where的查询条件</param>
		///<returns>返回行数</returns>
		public int GetRecordCount(string where, params DbParameter[] whereParameters)
		{
			int recordCount = 0;
			Database db = DatabaseFactory.CreateDatabase(connectionName);
            DbCommand cmd = db.GetSqlStringCommand("SELECT COUNT(1) FROM dbo.AttachmentTypeInfo "+ where);
            if(whereParameters != null && whereParameters.Length > 0)
			{
            	cmd.Parameters.AddRange(whereParameters);
			}
			using (IDataReader dr = db.ExecuteReader(cmd))
            {
				if(dr != null)
				{
					if (dr.Read()) 
					{
						if(!dr.IsDBNull(0)) recordCount = dr.GetInt32(0);
					}
				}
			}
			return recordCount;
		}
		
		///<summary>
		/// 将IDataReader转换为实体。
		///</summary>
		///<param name="dr">IDataReader当前待读取的行</param>
		///<returns>返回AttachmentTypeInfo实例</returns>
		private AttachmentTypeInfo BuildEntity(IDataReader dr)
		{
			AttachmentTypeInfo attachmentTypeInfo = new AttachmentTypeInfo();
			if(dr["ID"] != DBNull.Value) attachmentTypeInfo.ID = dr.GetString(dr.GetOrdinal("ID"));
			if(dr["TypeName"] != DBNull.Value) attachmentTypeInfo.TypeName = dr.GetString(dr.GetOrdinal("TypeName"));
			if(dr["Description"] != DBNull.Value) attachmentTypeInfo.Description = dr.GetString(dr.GetOrdinal("Description"));
			if(dr["AllowExt"] != DBNull.Value) attachmentTypeInfo.AllowExt = dr.GetString(dr.GetOrdinal("AllowExt"));
			if(dr["MaxFileSize"] != DBNull.Value) attachmentTypeInfo.MaxFileSize = dr.GetDecimal(dr.GetOrdinal("MaxFileSize"));
			if(dr["SaveDirName"] != DBNull.Value) attachmentTypeInfo.SaveDirName = dr.GetString(dr.GetOrdinal("SaveDirName"));
			return attachmentTypeInfo;
		}
		#endregion
		
		#region 扩展方法
		#endregion
    }
}